package com.zaofan.service;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.zaofan.bean.Account;
import com.zaofan.bean.AccountLog;
import com.zaofan.bean.Address;
import com.zaofan.bean.Comment;
import com.zaofan.bean.CouponUser;
import com.zaofan.bean.OpenArea;
import com.zaofan.bean.OpenBuilder;
import com.zaofan.bean.OrderChar;
import com.zaofan.bean.OrderDetail;
import com.zaofan.bean.OrderInfo;
import com.zaofan.bean.OrderNotice;
import com.zaofan.bean.OrderPay;
import com.zaofan.bean.Product;
import com.zaofan.bean.ProductAttr;
import com.zaofan.bean.ProductClass;
import com.zaofan.bean.ProductList;
import com.zaofan.bean.Reply;
import com.zaofan.bean.ScoreAve;
import com.zaofan.bean.Shop;
import com.zaofan.cache.CacheManage;
import com.zaofan.constant.StateConstant;
import com.zaofan.constant.TypeConstant;
import com.zaofan.constant.UrlConstant;
import com.zaofan.dao.OrderDetailDao;
import com.zaofan.dao.OrderInfoDao;
import com.zaofan.dao.ProductClassDao;
import com.zaofan.exception.ServiceException;
import com.zaofan.mobile.bean.PayBean;
import com.zaofan.mobile.bean.SubmitItem;
import com.zaofan.mobile.bean.SubmitPackageOrder;
import com.zaofan.mobile.bean.UserOrder;
import com.zaofan.mobile.bean.UserOrderItem;
import com.zaofan.service.remote.RemoteService;
import com.zaofan.utils.C3p0DataSourceUtils;
import com.zaofan.utils.HTTPClient;
import com.zaofan.utils.PayUtils;
import com.zaofan.utils.SqlBuilderUtils;
import com.zaofan.utils.WebUtils;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.zaofan.utils.C3p0DataSourceUtils.getDataSource;

public class OrderInfoService {

	private static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(OrderInfoService.class);

	private OrderInfoDao orderInfoDao = new OrderInfoDao();
	private OrderDetailDao orderDetailDao = new OrderDetailDao();
	private AccountService accountService = new AccountService();
	private AddressService addressService = new AddressService();
	private ProductService productService = new ProductService();
	private ProductAttrService productAttrService = new ProductAttrService();
	private ShopService shopService = new ShopService();
	private ActivityService activityService = new ActivityService();
	private CommentService commentService = new CommentService();
	private ReplyService replyService = new ReplyService();
	private PackageOrderInfoService packageOrderInfoService = new PackageOrderInfoService();
	private ProductClassDao productClassDao = new ProductClassDao();
	private OrderDetailService orderDetailService = new OrderDetailService();
	private OpenBuilderService openBuilderService = new OpenBuilderService();
	private OpenAreaService openAreaService = new OpenAreaService();

	public void delete(OrderInfo orderInfo) {
		orderInfoDao.del(orderInfo);
	}

	public void add(OrderInfo orderInfo) {
		orderInfoDao.add(orderInfo);
	}

	public void update(OrderInfo orderInfo) {
		orderInfoDao.update(orderInfo);
	}

	public List<OrderInfo> findAll(Map<String, Object> params) {
		List<OrderInfo> data = orderInfoDao.findAll(params);
		if (data == null) {
			data = new ArrayList<>();
		}
		return data;
	}

	public Long count(Map<String, Object> params) {
		return orderInfoDao.count(params);
	}

	public OrderInfo findById(Integer id) {
		Map<String, Object> params = new HashMap<>();
		params.put("id", id);
		return orderInfoDao.findOne(params);
	}

	public OrderInfo findParentByOrderNo(String orderNo) {
		Map<String, Object> params = new HashMap<>();
		params.put("orderNo", orderNo);
		params.put("parentId",0);
		return orderInfoDao.findOne(params);
	}

	public boolean isFirstOrder(int id) {
		return orderInfoDao.isFirstOrder(id);
	}

	private boolean duplicateDeviceId(String deviceId) {
		if (deviceId == null) {
			return false;
		}
		return !orderInfoDao.isFirstDevice(deviceId);
	}

	private boolean duplicateUserMobile(String userMobile) {
		return !orderInfoDao.isFirstUserMobile(userMobile);
	}

	public boolean isFirstPayedOrder(int id) {
		return orderInfoDao.isFirstPayedOrder(id);
	}

	public List<UserOrder> findByAcccountId(Integer accountId, Integer begin, Integer end) {
		List<UserOrder> orders = new ArrayList<>();
		List<OrderInfo> orderInfos = orderInfoDao.findByAcccountId(accountId, begin, end);
		for (OrderInfo info : orderInfos) {
			UserOrder order = dealOrder(info);
			if (order != null) {
				orders.add(order);
			}
		}
		return orders;
	}
	public List<UserOrder> findByAcccountId2(Integer accountId, Integer begin, Integer end) {
		List<UserOrder> orders = new ArrayList<>();
		List<OrderInfo> orderInfos = orderInfoDao.findByAcccountId(accountId, begin, end);
		for (OrderInfo info : orderInfos) {
			UserOrder order = dealOrder2(info);
			if (order != null) {
				orders.add(order);
			}
		}
		return orders;
	}

	public void deleteByOrderId(Integer orderId) throws ServiceException, SQLException {
		OrderInfo info = orderInfoDao.findById(orderId);
		if (info == null) {
			throw new ServiceException("订单数据错误!!");
		}
		if (info.getIsPackage() == 0) {
			// 普通订单删除
			if (info.getState() == StateConstant.ORDER_STATE_ORDER
					&& info.getState() == StateConstant.ORDER_PAY_STATE_UN_PAY) {
				Connection connection = C3p0DataSourceUtils.getConnection();
				connection.setAutoCommit(false);
				try {
					String sql = SqlBuilderUtils.builderDelSql(OrderInfo.class, info);
					QueryRunner runner = new QueryRunner();
					runner.update(connection, sql);
					if (info.getIsMuti() == 1) {
						List<OrderInfo> children = findByParentId(orderId);
						if (children != null && children.size() > 0) {
							for (OrderInfo order : children) {
								sql = SqlBuilderUtils.builderDelSql(OrderInfo.class, order);
								runner.update(connection, sql);
								sql = "delete FROM " + OrderDetail.class.getSimpleName().toLowerCase()
										+ " where orderId = " + order.getId();
								runner.update(connection, sql);
							}
						}
					} else {
						sql = "delete FROM " + OrderDetail.class.getSimpleName().toLowerCase() + " where orderId = "
								+ orderId;
						runner.update(connection, sql);
					}
					connection.commit();
				} catch (Exception e) {
					e.printStackTrace();
					connection.rollback();
				} finally {
					connection.close();
				}
			} else {
				throw new ServiceException("订单无法删除!!");
			}
		} else {
			// 周套餐删除操作 删除订单 用户加上金额
			if (info.getState() == StateConstant.ORDER_STATE_ORDER
					&& info.getState() == StateConstant.ORDER_PAY_STATE_UN_PAY) {
				Connection connection = C3p0DataSourceUtils.getConnection();
				connection.setAutoCommit(false);
				try {
					QueryRunner runner = new QueryRunner();
					String sql = "update orderinfo set isTimeOut = 1 where orderNo='" + info.getOrderNo() + "'";
					runner.update(connection, sql);
					connection.commit();
				} catch (Exception e) {
					e.printStackTrace();
					connection.rollback();
				} finally {
					connection.close();
				}
			} else {
				throw new ServiceException("订单无法删除!!");
			}
		}

	}

	public List<OrderInfo> findByParentId(Integer orderId) {
		return orderInfoDao.findByParentId(orderId);
	}

	public UserOrder getOrderInfo(Integer orderId)
			throws ServiceException, IllegalAccessException, InvocationTargetException {
		OrderInfo info = orderInfoDao.findById(orderId);
		if (info == null) {
			throw new ServiceException("订单数据有误!!");
		}
		if (info.getParentId() != 0) {
			throw new ServiceException("订单数据有误!!");
		}
		UserOrder order = dealOneOrder(info);
		if (info.getIsPackage() == 1) {
			//order.setSendDate("周套餐");
			order.setSendDate(info.getSendDate());
		}
		if (order.getState() == StateConstant.ORDER_STATE_COMMENT) {
			Comment comment = commentService.findByOrderId(order.getId());
			com.zaofan.mobile.bean.Comment data = new com.zaofan.mobile.bean.Comment();
			BeanUtils.copyProperties(data, comment);
			if (comment.getIsReply() == 1) {
				Reply reply = replyService.findByCommentId(comment.getId());
				data.setReply(reply);
			}
			if (comment.getUserPhoto() != null && !"".equals(comment.getUserPhoto())) {
				data.setUserPhoto(UrlConstant.HOST_URL + comment.getUserPhoto());
			}
			order.setComment(data);
		}
		return order;
	}

	public UserOrder dealOneOrder(OrderInfo info) {
		UserOrder order = null;
		try {
			order = new UserOrder();
			List<UserOrderItem> orderItem;
			UserOrderItem item = null;
			BeanUtils.copyProperties(order, info);
			order.setDetail(info.getAreaName() + info.getBuilderName() + info.getFloorNo() + info.getDetail());
			List<OrderDetail> orderDetails;
			if (info.getIsMuti() == 0) {
				// 当订单处理
				orderDetails = orderDetailService.findByOrderId(info.getId());
			} else {
				// 多订单处理
				orderDetails = orderDetailDao.getOrderDetailsWithSendDate(info.getId());
			}
			orderItem = new ArrayList<>();
			for (OrderDetail detail : orderDetails) {
				item = new UserOrderItem();
				item.setSendDate(detail.getSendDate());
				BeanUtils.copyProperties(item, detail);
				item.setProductImage(detail.getProductImage());
				orderItem.add(item);
			}
			Collections.reverse(orderItem);
			order.setGoods(orderItem);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return order;
	}

	public UserOrder dealOrder(OrderInfo info) {
		UserOrder order = null;
		try {
			order = new UserOrder();
			List<UserOrderItem> orderItem;
			UserOrderItem item = null;
			BeanUtils.copyProperties(order, info);
			if (info.getIsMuti() == 0) {
				// 当订单处理
				List<OrderDetail> orderDetails = orderDetailService.findByOrderId(info.getId());
				orderItem = new ArrayList<>();
				for (OrderDetail detail : orderDetails) {
					item = new UserOrderItem();
					BeanUtils.copyProperties(item, detail);
					orderItem.add(item);
				}
				Map<String, List<UserOrderItem>> temp = new HashMap<>();
				temp.put(info.getShopName(), orderItem);
				order.setShopData(temp);
			} else {
				// 多订单处理
				List<OrderDetail> orderDetails = orderDetailDao.getOrderDetailsWithSendDate(info.getId());
				orderItem = new ArrayList<>();
				Map<String, List<UserOrderItem>> temp = new HashMap<>();
				for (OrderDetail detail : orderDetails) {
					item = new UserOrderItem();
					BeanUtils.copyProperties(item, detail);
					if (temp.containsKey(item.getShopName())) {
						temp.get(item.getShopName()).add(item);
					} else {
						orderItem = new ArrayList<>();
						orderItem.add(item);
						temp.put(item.getShopName(), orderItem);
					}
				}
				if (order.getIsPackage() == 1) {
					order.setSendDate("周套餐");
				}
				order.setShopData(temp);
			}

		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return order;
	}

	public UserOrder dealOrder2(OrderInfo info) {
		UserOrder order = null;
		try {
			order = new UserOrder();
			List<UserOrderItem> orderItem;
			UserOrderItem item = null;
			BeanUtils.copyProperties(order, info);
			if (info.getIsMuti() == 0) {
				// 当订单处理
				List<OrderDetail> orderDetails = orderDetailService.findByOrderId(info.getId());
				orderItem = new ArrayList<>();
				for (OrderDetail detail : orderDetails) {
					item = new UserOrderItem();
					BeanUtils.copyProperties(item, detail);
					orderItem.add(item);
				}
				Map<String, List<UserOrderItem>> temp = new HashMap<>();
				temp.put(info.getShopName(), orderItem);
				order.setShopData(temp);
			} else {
				// 多订单处理
				List<OrderDetail> orderDetails = orderDetailService.findByParentOrderId(info.getId());
				orderItem = new ArrayList<>();
				Map<String, List<UserOrderItem>> temp = new HashMap<>();
				for (OrderDetail detail : orderDetails) {
					item = new UserOrderItem();
					BeanUtils.copyProperties(item, detail);
					if (temp.containsKey(item.getShopName())) {
						temp.get(item.getShopName()).add(item);
					} else {
						orderItem = new ArrayList<>();
						orderItem.add(item);
						temp.put(item.getShopName(), orderItem);
					}
				}
				order.setShopData(temp);
			}

		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return order;
	}

	public void addOrderComment(Comment comment) throws ServiceException, SQLException {
		if (comment.getContent() == null || "".equals(comment.getContent())) {
			throw new ServiceException("请填写评价内容!!");
		}
		if (comment.getOrderId() == 0) {
			throw new ServiceException("订单数据有误!!");
		}
		OrderInfo orderInfo = findById(comment.getOrderId());
		if (orderInfo == null) {
			throw new ServiceException("订单数据有误!!");
		}
		if (orderInfo.getPayState() == StateConstant.ORDER_PAY_STATE_UN_PAY
				|| orderInfo.getPayState() == StateConstant.ORDER_PAY_STATE_PAY_CHECK
				|| orderInfo.getState() == StateConstant.ORDER_STATE_ORDER
				|| orderInfo.getState() == StateConstant.ORDER_STATE_COMMENT) {
			throw new ServiceException("该订单暂时无法进行评论!!");
		}
		comment.setOrderNo(orderInfo.getOrderNo());
		comment.setOrderTime(orderInfo.getOrderTime());
		Account account = accountService.findById(comment.getAccountId());
		if (account == null) {
			throw new ServiceException("用户信息有误!!");
		}
		if (orderInfo.getAccountId() != account.getId()) {
			throw new ServiceException("订单与用户匹配有误!!");
		}

		comment.setAccountMobile(account.getMobile());
		comment.setIsReply(0);
		comment.setAve((comment.getTasteScore() + comment.getSpeedScore()) / 2);
		if (comment.getAve() < 3) {
			comment.setCommentType("差评");
			// 发送 推送到管理平台 提示管理处理
		} else if (comment.getAve() == 3) {
			comment.setCommentType("中评");
			// 发送 推送到管理平台 提示管理处理
		} else {
			comment.setCommentType("好评");
		}
		comment.setAddTime(new Date());
		comment.setNickName(account.getNickname());
		comment.setUserPhoto(account.getPhoto());
		Connection connection = C3p0DataSourceUtils.getConnection();
		connection.setAutoCommit(false);
		Shop shop;
		try {
			QueryRunner runner = new QueryRunner();
			String sql = "";
			if (orderInfo.getIsMuti() == 0) {
				comment.setShopId(orderInfo.getShopId());
				sql = SqlBuilderUtils.builderInsertSql(Comment.class, comment);
				System.err.println(sql);
				runner.update(connection, sql);
				shop = shopService.findById(orderInfo.getShopId());
				ScoreAve ave = commentService.avgScore(orderInfo.getShopId());
				float score = (float) ((ave.getSum() + comment.getAve()) / (ave.getCount() + 1));
				sql = SqlBuilderUtils.builderUpdateSql(Shop.class, shop);
				shop.setShopScore(new BigDecimal(score));
				runner.update(connection, sql);
			} else {
				List<OrderInfo> orderInfos = findByParentId(orderInfo.getId());
				for (OrderInfo info : orderInfos) {
					comment.setShopId(info.getShopId());
					sql = SqlBuilderUtils.builderInsertSql(Comment.class, comment);
					System.err.println(sql);
					runner.update(connection, sql);
					shop = shopService.findById(info.getShopId());
					ScoreAve ave = commentService.avgScore(orderInfo.getShopId());
					float score = (float) ((ave.getSum() + comment.getAve()) / (ave.getCount() + 1));
					sql = SqlBuilderUtils.builderUpdateSql(Shop.class, shop);
					shop.setShopScore(new BigDecimal(score));
					runner.update(connection, sql);
				}
			}
			orderInfo.setState(StateConstant.ORDER_STATE_COMMENT);
			sql = "update " + OrderInfo.class.getSimpleName().toLowerCase() + " set state = "
					+ StateConstant.ORDER_STATE_COMMENT + " where orderNo = '" + orderInfo.getOrderNo() + "'";
			runner.update(connection, sql);
			connection.commit();
		} catch (Exception e) {
			e.printStackTrace();
			connection.rollback();
		} finally {
			connection.close();
		}
	}

	public OrderInfo findByOrderNo(String orderNo) {
		Map<String, Object> params = new HashMap<>();
		params.put("orderNo", orderNo);
		return orderInfoDao.findOne(params);
	}



	public void updateOrderPayState(String orderNo) throws ServiceException {
		if (orderNo == null) {
			throw new ServiceException("订单信息有误!!");
		}
		OrderInfo info = findByOrderNo(orderNo);
		if (info == null) {
			throw new ServiceException("订单信息有误!!");
		}
		orderInfoDao.updateaOrderPayState(orderNo);
	}

	public PayBean submitPackageOrder(String orderInfo,String platform) throws Exception {
		PayBean payBean = new PayBean();

		try {
			Gson gson = new Gson();

			SubmitPackageOrder submitOrder = gson.fromJson(orderInfo, new TypeToken<SubmitPackageOrder>() {
			}.getType());
			int userId = submitOrder.getUserId();

			Account account = accountService.findById(userId);
			if (account == null) {
				throw new ServiceException("账户信息有误!!");
			}
			Address address = addressService.findById(submitOrder.getAddress());
			if (address == null) {
				throw new ServiceException("账户收获信息有误!!");
			}
			// 地址校验
			OpenBuilder builder = openBuilderService.findById(address.getBuilderId());
			if (builder.getState() == 1) {
				throw new ServiceException("配送大楼地址信息过期,请重新编辑");
			}

			OpenArea openArea = openAreaService.findById(builder.getAreaId());
			if(openArea.getState() == 1){
				throw new ServiceException("该区域暂未开始派送，陆续开启中敬请关注");
			}

			if (!WebUtils.isMobileNO(submitOrder.getMobile())) {
				throw new ServiceException("请输入正确的手机号码!!");
			}
			Map<String, List<SubmitItem>> shopData = submitOrder.getShopData();
			if (shopData == null) {
				throw new ServiceException("订单信息有误!!");
			}

			Connection connection = C3p0DataSourceUtils.getConnection();
			QueryRunner runner = new QueryRunner();
			try {
				connection.setAutoCommit(false);
				OrderInfo order = new OrderInfo();
				order.setAccountId(account.getId());
				order.setAccountMobile(account.getMobile());
				order.setIsMuti(0);
				order.setAreaId(address.getAreaId());
				order.setAreaName(address.getAreaName());
				order.setBuilderId(address.getBuilderId());
				order.setBuilderName(address.getBuilderName());
				order.setCityId(address.getCityId());
				order.setCityName(address.getCityName());
				order.setConsignee(address.getConsignee());
				order.setDetail(address.getDetail());
				order.setFloorNo(address.getFloorNo());
				order.setUserMobile(address.getPhone());
				order.setOrderTime(new Date());
				order.setPayState(StateConstant.ORDER_PAY_STATE_UN_PAY);
				order.setRemark(submitOrder.getMark());
				order.setSendTime(submitOrder.getTime());

				order.setState(StateConstant.ORDER_STATE_ORDER);
				order.setPlatform(account.getPlatform());
				order.setPayFree(new BigDecimal(0));
				order.setFreePrice(new BigDecimal(0));
				order.setIsActivity(0);
				order.setIsMuti(1);
				order.setOrderNo(WebUtils.getOrderId(account.getId()));
				order.setIsPackage(1);
				order.setDeviceId(account.getDeviceId());



				Integer shopId = 20;
				Shop shop = shopService.findById(shopId);
				order.setShopId(shopId);
				order.setShopName(shop.getShopName());

				order.setExpressLableId(shop.getExpressLableId());
				order.setExpressLableName(shop.getExpressLableName());

				List<OrderDetail> details = null;
				int total = 0;
				BigDecimal price = new BigDecimal(0);
				String orderIds = "";
				String orderNos = "";
				int totalCount = 0;
				BigDecimal totalPrice = new BigDecimal(0);
				OrderInfo itemOrder = null;
				Map<OrderInfo, List<OrderDetail>> orders = new HashMap<>();
				for (Map.Entry<String, List<SubmitItem>> entry : shopData.entrySet()) {
					int itemCount = 0;
					BigDecimal itemPrice = new BigDecimal(0);
					String key = entry.getKey();
					List<SubmitItem> submitItems = entry.getValue();
					Integer categoryId = Integer.valueOf(key.split(",")[0]);
					String day = key.split(",")[1];
					Date sendDate = WebUtils.parseDate(day);
					if (sendDate == null) {
						throw new ServiceException("送货日期有误!!");
					}
					Date now = new Date();
					if (sendDate.before(now)) {
						throw new ServiceException("送货日期有误!!");
					}
					itemOrder = new OrderInfo();
					BeanUtils.copyProperties(itemOrder, order);
					itemOrder.setIsMuti(0);
					orderInfo += order.getOrderNo() + ",";
					itemOrder.setSendDate(day);
					itemOrder.setShopId(shopId);
					itemOrder.setShopName(shop.getShopName());

					List<SubmitItem> items = entry.getValue();
					if (items.isEmpty()) {
						throw new ServiceException("请选择购买商品!!");
					}
					itemOrder.setExpressLableId(shop.getExpressLableId());
					itemOrder.setExpressLableName(shop.getExpressLableName());
					itemOrder.setActivityIds("");
					itemOrder.setActivityName("");

					order.setActivityIds("");
					order.setActivityName("");

					OrderDetail orderDetail = null;
					details = new ArrayList<>();
					for (SubmitItem item2 : submitItems) {
						if (isProductClassClosed(item2.getGoodId(), sendDate)) {
							throw new ServiceException("产品:" + item2.getGoodName() + "所属分类已打烊，无法预订次日订单。");
						}

						orderDetail = new OrderDetail();
						Integer productId = item2.getGoodId();
						Product product = productService.findById(productId);
						if (product == null) {
							throw new ServiceException("产品信息有误!!");
						}
						if (product.getCount() > 0) {
							if (product.getDateCount() + item2.getGoodAccount() > product.getCount()) {
								throw new ServiceException("产品:" + product.getName() + "当天已售完,请明天抓紧下手哦!!");
							}
						}
						if (product.getState() != 0) {
							throw new ServiceException("对不起， 商品：" + product.getName()+ "已下架");
						}
						orderDetail.setCategoryId(product.getCategoryId());
						orderDetail.setCategoryName(product.getCategoryName());
						orderDetail.setProductCount(item2.getGoodAccount());
						orderDetail.setProductId(productId);
						orderDetail.setProductImage(product.getImage());
						shop = shopService.findByProductId(productId);
						if (shop.getCityId() != address.getCityId()) {
							throw new ServiceException("产品:" + product.getName() + "是"
									+ shop.getCityName() + "的商品，与配送地址所在城市:" + address.getCityName() + "不符");
						}
						if (shop.getState() != 0) {
							throw new ServiceException("对不起， 商家：" + shop.getShopName()+ "已暂停营业");
						}
						orderDetail.setShopId(shop.getId());
						orderDetail.setShopName(shop.getShopName());
						orderDetail.setOrderNo(order.getOrderNo());
						String productName = product.getName();
						String attrName = "";

						//如果是酒店的话


						BigDecimal price1 = product.getPrice();
// TODO 酒店注释
//                        if ("酒店".equals(submitOrder.getMark())){
//                            price1=product.getHotelPrice();
//                        }

						if (item2.getAttributeInfo() != null) {
							for (String id : item2.getAttributeInfo().getAttriId().split(",")) {
								try {
									Integer i = Integer.valueOf(id);
									ProductAttr attr = productAttrService.findById(i);
									if (attr == null) {
										throw new ServiceException("产品属性信息有误!!");
									}
									if (attr.getProductId() != productId) {
										throw new ServiceException("产品属性信息有误!!");
									}
									attrName += attr.getAttrValue() + ",";
									price1 = price1.add(attr.getPrice());
								} catch (Exception e) {
									throw new ServiceException("产品属性信息有误!!");
								}
							}

						}
						if (!"".equals(attrName)) {
							attrName = attrName.substring(0, attrName.length() - 1);
							productName += "(" + attrName + ")";
							orderDetail.setAttributeInfo(attrName);
							orderDetail.setAttributeIds(item2.getAttributeInfo().getAttriId());
						}
						totalCount += item2.getGoodAccount();
						itemCount += item2.getGoodAccount();

						orderDetail.setProductName(productName);
						orderDetail.setProductPrice(price1);
						orderDetail.setTotalPrice(orderDetail.getProductPrice().multiply(new BigDecimal(item2.getGoodAccount())));

						if(item2.getProductClassId()!=null){
							orderDetail.setProductClassId(item2.getProductClassId());
						}else if(item2.getGoodProductClassId()!=null){
							orderDetail.setProductClassId(item2.getGoodProductClassId());
						}

						orderDetail.setFree(new BigDecimal(0));


						totalPrice = totalPrice.add(orderDetail.getTotalPrice());
						itemPrice = itemPrice.add(orderDetail.getTotalPrice().subtract(orderDetail.getFree()));

						details.add(orderDetail);
					}
					if (totalPrice.compareTo(shop.getMinPrice()) < 0) {
						throw new ServiceException("对不起,您购买的商品还未达到最低送餐费!!");
					}

					itemOrder.setPayPrice(itemPrice);
					itemOrder.setQuantity(itemCount);
					itemOrder.setTotalPrice(itemPrice);

					//将OrderDetail里面的价格都相加起来形成优惠价格
					BigDecimal detailFree = new BigDecimal(0);
					for (OrderDetail detail : details) {
						detailFree = detailFree.add(detail.getFree());
					}
					itemOrder.setFreePrice(detailFree);


					itemOrder.setDeviceId(order.getDeviceId());
					orders.put(itemOrder, details);

				}

				//设置总订单优惠
				//BigDecimal freePrice = new BigDecimal(0);
				BigDecimal totalFree = new BigDecimal(0);
				for (Map.Entry<OrderInfo, List<OrderDetail>> entry : orders.entrySet()) {
					totalFree = totalFree.add(entry.getKey().getFreePrice());
				}
				BigDecimal freePrice = totalFree;

				order.setIsCoupon(0);

				 logger.info("---------------submit"+submitOrder.toString());
				if (null!= submitOrder.getCouponId() && null!=submitOrder.getCouponUserId() ){
					RemoteService remoteService =new RemoteService();
					CouponUser couponUser = remoteService.LockCoupon(totalPrice,order.getAccountId(),submitOrder.getCouponUserId(),submitOrder.getCouponId());
					if (null==couponUser){
						throw new ServiceException("锁定优惠券失败！");
					}else{
						order.setIsCoupon(1);
						order.setCouponUserid(submitOrder.getCouponUserId());
						order.setCouponId(submitOrder.getCouponId());
						order.setActivityName("使用了优惠券");
						order.setFreePrice(couponUser.getValue());
						freePrice=couponUser.getValue();
					}

				}

				order.setPayPrice(totalPrice.subtract(freePrice));
				order.setQuantity(totalCount);
				order.setTotalPrice(totalPrice);

				order.setFreePrice(freePrice);


				if (!"".equals(orderIds)) {
					orderIds = orderIds.substring(0, orderIds.length() - 1);
				}
				if (!"".equals(orderNos)) {
					orderNos = orderNos.substring(0, orderNos.length() - 1);
				}

				String sql = SqlBuilderUtils.builderInsertSql(OrderInfo.class, order);
				logger.info(sql);

				runner.update(connection, sql);
				// 获取新增记录的自增主键
				int totalOrderId = 0;
				// 获取新增记录的自增主键
				try {
					Long id = (Long) runner.query(connection, "SELECT LAST_INSERT_ID()", new ScalarHandler<Long>());
					totalOrderId = id.intValue();
				} catch (Exception e) {
					BigInteger id = (BigInteger) runner.query(connection, "SELECT LAST_INSERT_ID()",
							new ScalarHandler<BigInteger>());
					totalOrderId = id.intValue();
				}

				logger.info("插入子订单前的父订单ID"+totalOrderId);


				for (Map.Entry<OrderInfo, List<OrderDetail>> entry : orders.entrySet()) {
					OrderInfo info = entry.getKey();

					info.setParentId(totalOrderId);
					info.setShopId(entry.getValue().get(0).getShopId());
					info.setShopName(entry.getValue().get(0).getShopName());
					sql = SqlBuilderUtils.builderInsertSql(OrderInfo.class, info);
					System.err.println(sql);

					runner.update(connection, sql);
					// 获取新增记录的自增主键
					int itemId = 0;
					try {
						Long itemIdb = (Long) runner.query(connection, "SELECT LAST_INSERT_ID()",
								new ScalarHandler<Long>());
						itemId = itemIdb.intValue();
					} catch (Exception e) {
						BigInteger itemIdb = (BigInteger) runner.query(connection, "SELECT LAST_INSERT_ID()",
								new ScalarHandler<BigInteger>());
						itemId = itemIdb.intValue();
					}
					for (OrderDetail detail2 : entry.getValue()) {
						detail2.setOrderId(itemId);
						detail2.setParentOrderId(totalOrderId);
						sql = SqlBuilderUtils.builderInsertSql(OrderDetail.class, detail2);
						logger.info(sql);
						runner.update(connection, sql);
					}

				}

				logger.info("返回PayBean的OrderId"+totalOrderId);

				payBean.setOrderId(totalOrderId);
				payBean.setOrderNo(order.getOrderNo());
				payBean.setOrderTime(order.getOrderTime());
				payBean.setAccountMoney(account.getMoney());

				payBean.setFreePrice(order.getFreePrice());
				payBean.setGoodsCount(totalCount);
				payBean.setTotalPrice(totalPrice);

				account.setBuilderId(address.getBuilderId());
				account.setBuilderName(address.getBuilderName());
				account.setCityId(address.getCityId());
				account.setCityName(address.getCityName());
				sql = SqlBuilderUtils.builderUpdateSql(Account.class, account);
				logger.info(sql);
				runner.update(connection, sql);
				connection.commit();
			} catch (ServiceException serviceException) {
				connection.rollback();
				serviceException.printStackTrace();
				throw serviceException;
			} catch (Exception e) {
				connection.rollback();
				e.printStackTrace();
				throw e;
			} finally {
				connection.close();
			}
		} catch (ServiceException serviceException) {
			serviceException.printStackTrace();
			throw serviceException;
		} catch (Exception exception) {
			exception.printStackTrace();
			throw exception;
		}
		return payBean;
	}

	/**
	 * 判断某个用户是否下过某个分类某天的单
	 * @param date
	 * @param productClassId
	 * @param userId
     * @return
     */
	private boolean isHaveOrder(String date, int productClassId,int userId) throws SQLException {
		String sql = "select * from orderdetail \n" +
				"\twhere orderId in(\n" +
				"\t\tselect id from orderinfo where isTimeOut=0 and state !=3 \n" +
				"\t\tand sendDate='" + date + "' and isMuti=0\n" +
				"\t\tand accountId='" + userId + "'\n" +
				"\t)\n" +
				"\tand productClassId=" + productClassId + ";";
		System.err.println(sql);
		QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
		List<OrderDetail> orderDetailList=  runner.query(sql,new BeanListHandler<>(OrderDetail.class));
		if(orderDetailList.size() != 0){
			return true;
		}
		return false;
	}

	/**
	 * 查询某个商品某个日期的销售数量
	 * @param date
	 * @param id
     * @return
     */
	private int findDayCountByProductId(String date, int id) throws SQLException {

		String sql = "select * from orderdetail \n" +
				"\twhere orderId in(\n" +
				"\t\tselect id from orderinfo where isTimeOut=0 and sendDate='"+ date +"' and state !=3 \n" +
				"\t)\n" +
				"\tand productId = '" + id + "';";
		System.err.println(sql);
		QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
		List<OrderDetail> orderDetailList=  runner.query(sql,new BeanListHandler<>(OrderDetail.class));
		int dayCount = 0;
		for (OrderDetail detail : orderDetailList) {
			dayCount += detail.getProductCount();
		}
		return dayCount;
	}


	/**
	 * 根据产品的 id, 判断归属的分类是否打烊
	 *
	 * @param goodId
	 * @return
	 * @throws ServiceException
	 */
	private boolean isProductClassClosed(int goodId, Date sendDate) throws ServiceException {
		Calendar tomorrow = Calendar.getInstance();
		tomorrow.add(Calendar.DAY_OF_YEAR, 1);
		tomorrow.set(Calendar.HOUR_OF_DAY, 0);
		tomorrow.set(Calendar.MINUTE, 0);
		tomorrow.set(Calendar.SECOND, 0);
		if (sendDate.after(new Date(tomorrow.getTimeInMillis()))) {
			return false;
		}


		Product product = productService.findById(goodId);
		if (product == null) {
			throw new ServiceException("产品信息有误!!");
		}
		Map<String, Object> params = new HashMap<>();
		params.put("id", product.getProductClassId());
		ProductClass productClass = productClassDao.findOne(params);
		if (productClass == null) { //周套餐
			return false;
		}
		String[] close = productClass.getClosetime().split(":");

		Calendar now = Calendar.getInstance();
		Calendar calendar = Calendar.getInstance();
		calendar.set(Calendar.HOUR_OF_DAY, Integer.valueOf(close[0]));
		calendar.set(Calendar.MINUTE, Integer.valueOf(close[1]));
		calendar.set(Calendar.SECOND, Integer.valueOf(close[2]));
		return now.after(calendar);

	}

	public void payOrderByAccountMoney(String orderNo) throws ServiceException, SQLException {
		OrderInfo order = findByOrderNo(orderNo);
		Product product;
		Shop shop;
		List<OrderDetail> details = new ArrayList<>();
		if (order == null) {
			throw new ServiceException("订单信息错误!!");
		}
		if (order.getPayState() == 2) {
			return;
		}
		Account account = accountService.findById(order.getAccountId());
		if (account == null) {
			throw new ServiceException("账户信息错误!!");
		}
		if (account.getMoney().compareTo((order.getTotalPrice().subtract(order.getFreePrice()))) < 0) {
			throw new ServiceException("账户余额不足!!");
		}
		Connection connection = C3p0DataSourceUtils.getConnection();
		try {
			connection.setAutoCommit(false);
			QueryRunner runner = new QueryRunner();
			String sql = "";
			if (CacheManage.setting.getRefereeReturn() == 1
					&& isFirstPayedOrder(account.getId())) {//推荐人得2元
				if (account.getReferee() != null && !account.getReferee().trim().equals("")) {
					Account refereeAccount = accountService.findByMobile(account.getReferee().trim());
					if (refereeAccount != null) {
						BigDecimal beforeMoney =  refereeAccount.getMoney();
						refereeAccount.setMoney(beforeMoney.add(CacheManage.setting.getRefereeReturnMoney()));
						sql = SqlBuilderUtils.builderUpdateSql(Account.class, refereeAccount);
						runner.update(connection, sql);

						//记录
						AccountLog log = new AccountLog();
						log.setAccountId(refereeAccount.getId());
						log.setAccountMobile(refereeAccount.getMobile());
						log.setAddTime(new Date());
						log.setMoney(CacheManage.setting.getRefereeReturnMoney());
						log.setBeforeMoney(beforeMoney);
						log.setAfterMoney(refereeAccount.getMoney());
						log.setType(TypeConstant.log_type_referee_return);
						log.setMark("被推荐人 "+ account.getMobile() +" 已下单");
						sql = SqlBuilderUtils.builderInsertSql(AccountLog.class, log);
						runner.update(connection, sql);
					}else{
						logger.error("推荐人账户为空 id:" + account.getId());
					}
				}else{
					logger.error("推荐人手机账号找不到 id:" + account.getId());
				}
				//厦门 地推内测用户， 首单返5元

			}else{
				logger.error("不是第一次下单 id:" + account.getId());
			}


			if (order.getIsMuti() == 1) {
				details = orderDetailService.findByParentOrderId(order.getId());
			} else {
				details = orderDetailService.findByOrderId(order.getId());
			}
			if (details != null && details.size() > 0){
				for (OrderDetail detail : details) {
					product = productService.findById(detail.getProductId());
					product.setDateCount(product.getDateCount() + detail.getProductCount());
					product.setSellcount(product.getSellcount() + detail.getProductCount());
					shop = shopService.findById(product.getShopId());
					shop.setMonthCount(shop.getMonthCount() + detail.getProductCount());
					sql = SqlBuilderUtils.builderUpdateSql(Product.class, product);
					runner.update(connection, sql);
					sql = SqlBuilderUtils.builderUpdateSql(Shop.class, shop);
					runner.update(connection, sql);
				}
			}
			// 更新订单状态
			sql = "update " + OrderInfo.class.getSimpleName().toLowerCase() + " set payState = "
					+ StateConstant.ORDER_PAY_STATE_PAY + ",state = " + StateConstant.ORDER_STATE_PAY
					+ ",payType='余额支付' where orderNo = '" + order.getOrderNo() + "'";

			runner.update(connection, sql);
			// 增加订单支付记录
			OrderPay orderPay = new OrderPay();
			orderPay.setAddTime(new Date());
			orderPay.setOrderId(order.getId());
			orderPay.setOrderNo(order.getOrderNo());
			orderPay.setTradeNo(order.getOrderNo());
			orderPay.setPayMethod("账户余额支付");
			orderPay.setPayPrice(order.getPayPrice());
			orderPay.setPayState("支付成功");
			orderPay.setRemark("账户余额支付");
			sql = SqlBuilderUtils.builderInsertSql(OrderPay.class, orderPay);
			runner.update(connection, sql);
			// 增加用户账户记录
			AccountLog accountLog = new AccountLog();
			accountLog.setAccountId(account.getId());
			accountLog.setAccountMobile(account.getMobile());
			accountLog.setAddTime(new Date());
			accountLog.setMark("余额支付");
			accountLog.setType(TypeConstant.log_type_yu_er_pay);
			accountLog.setMoney(order.getPayPrice());
			accountLog.setBeforeMoney(account.getMoney());
			accountLog.setAfterMoney(account.getMoney().subtract(order.getPayPrice()));
			sql = SqlBuilderUtils.builderInsertSql(AccountLog.class, accountLog);
			runner.update(connection, sql);
			account.setMoney(account.getMoney().subtract(order.getPayPrice()));
			sql = SqlBuilderUtils.builderUpdateSql(Account.class, account);
			runner.update(connection, sql);

			connection.commit();

		} catch (Exception e) {
			connection.rollback();
		} finally {
			connection.close();
		}

		//当支付完成后,将早范订单流入新版物流管理系统接口
		try{
			List<OrderInfo> orderInfos = findByParentId(order.getId());
			for (OrderInfo info : orderInfos) {
				List<OrderDetail> orderDetails = orderDetailService.findByOrderId(info.getId());
				for (OrderDetail detail : orderDetails) {
					Map<String, String> map = new HashMap<>();
					map.put("sellerOrderNo",orderNo);
					map.put("price",info.getTotalPrice()+"");
					map.put("mobile",info.getUserMobile()+"");
					map.put("consignee",info.getConsignee()+"");
					map.put("productName",detail.getProductName()+"");
					map.put("productNum",detail.getProductCount()+"");
					map.put("productCategory",detail.getCategoryName());
					map.put("productId",detail.getProductId()+"");
					map.put("orderTime",info.getOrderTime()+"");
					map.put("sendTime",info.getSendDate()+ "/" + order.getSendTime());
					map.put("remark",info.getRemark()+"");
					map.put("address",info.getCityName()+"-"+info.getAreaName()+info.getBuilderName()+info.getFloorNo()+info.getDetail());
					OpenBuilder openBuilder = openBuilderService.findById(info.getBuilderId());
					map.put("builderDetail",openBuilder.getAddress());
					logger.info("进入物流的分类名"+detail.getCategoryName());
					HTTPClient.postMap("http://106.14.63.32:8081/admin/orders/inflowByZF", map);
				}
			}

		}catch (Exception e){
			e.printStackTrace();
		}
	}

	public List<OrderInfo> findAllOrder(Map<String, Object> params) {
		List<OrderInfo> orderInfos = findAll(params);
		return orderInfos;
	}

	public List<OrderInfo> exportTodaySendUserMobile() {
		Calendar calendar = Calendar.getInstance();
		int year = calendar.get(Calendar.YEAR);
		int month = calendar.get(Calendar.MONTH) + 1;
		int day = calendar.get(Calendar.DAY_OF_MONTH);
		try {
			String sql = "select * from " + OrderInfo.class.getSimpleName().toLowerCase() + " where sendDate = '" + year
					+ "-" + (month < 9 ? "0" + month : month + "") + "-" + (day < 9 ? "0" + day : day + "")
					+ "' and state in (" + StateConstant.ORDER_STATE_COMMENT + "," + StateConstant.ORDER_STATE_PAY
					+ ") and payState in ( " + StateConstant.ORDER_PAY_STATE_PAY + ","
					+ StateConstant.ORDER_PAY_STATE_ARCHIVE + ")" + " and isMuti = 0 group by accountMobile";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(OrderInfo.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public List<ProductList> findTomorrowListBySendData(String sendDate) {
		try {
			String sql = "select o.orderNo,o.id as orderId,d.productId,d.productName,sum(d.productCount) as productCount,d.shopName,d.shopId,d.productPrice from orderinfo o,orderdetail d where o.isMuti = 0 and o.id = d.orderId and o.payState in (2,3) and sendDate = '"
					+ sendDate + "' group by d.productName";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(ProductList.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public PayBean submitOrderByOrderId(String orderId) throws ServiceException {
		if (!WebUtils.isBefore10()) {
			throw new ServiceException("店铺已打烊!!");
		}
		Integer id = 0;
		try {
			id = Integer.valueOf(orderId);
		} catch (Exception e) {
			throw new ServiceException("订单信息有误,请刷新试试!!");
		}
		PayBean payBean = new PayBean();
		OrderInfo order = findById(id);
		if (order == null) {
			throw new ServiceException("订单信息有误,请刷新试试!!");
		}
		if (order.getParentId() != 0) {
			throw new ServiceException("订单信息有误,请刷新试试!!");
		}
		if (order.getState() != StateConstant.ORDER_STATE_ORDER
				|| order.getPayState() != StateConstant.ORDER_PAY_STATE_UN_PAY) {
			throw new ServiceException("订单已经支付,无法重新支付!!");
		}
		Account account = accountService.findById(order.getAccountId());
		if (account == null) {
			throw new ServiceException("订单信息有误!!");
		}

		if (order.getIsPackage() == 0) {
			// 普通订单
			payBean.setOrderId(id);
			payBean.setOrderNo(order.getOrderNo());
			payBean.setOrderTime(order.getOrderTime());
			payBean.setAccountMoney(account.getMoney());

			if (account.getMoney().compareTo(order.getPayPrice())  >= 0) {
				payBean.setAlipay(PayUtils.getAlipayString(payBean.getOrderNo(), "早范", "早范订单结算", order.getPayPrice()));
				payBean.setWeiPay(PayUtils.getWeiPayString(payBean.getOrderNo(), "早范", "早范订单结算", order.getPayPrice()));
			} else {
				if (account.getMoney().compareTo(new BigDecimal(0)) > 0) {
					payBean.setAlipay(PayUtils.getAlipayString(payBean.getOrderNo(), "早范", "早范订单结算",
							order.getPayPrice().subtract(account.getMoney())));
					payBean.setWeiPay(PayUtils.getWeiPayString(payBean.getOrderNo(), "早范", "早范订单结算",
							order.getPayPrice().subtract(account.getMoney())));
				} else {
					payBean.setAlipay(
							PayUtils.getAlipayString(payBean.getOrderNo(), "早范", "早范订单结算", order.getPayPrice()));
					payBean.setWeiPay(
							PayUtils.getWeiPayString(payBean.getOrderNo(), "早范", "早范订单结算", order.getPayPrice()));
				}
			}
			payBean.setFreePrice(order.getFreePrice());
			payBean.setGoodsCount(order.getQuantity());
			payBean.setTotalPrice(order.getTotalPrice());
		} else if (order.getIsPackage() == 1) {
			// 周订单
			payBean.setOrderId(id);
			payBean.setOrderNo(order.getOrderNo());
			payBean.setOrderTime(order.getOrderTime());
			payBean.setAccountMoney(account.getMoney());

			if (account.getMoney().compareTo(order.getPayPrice())  >= 0) {
				payBean.setAlipay(
						PayUtils.getAlipayPackageString(payBean.getOrderNo(), "早范", "早范订单结算", order.getPayPrice()));
				payBean.setWeiPay(PayUtils.getWeiPayPackage(payBean.getOrderNo(), "早范", "早范订单结算", order.getPayPrice()));
			} else {
				if (account.getMoney().compareTo(new BigDecimal(0)) > 0) {
					payBean.setAlipay(PayUtils.getAlipayPackageString(payBean.getOrderNo(), "早范", "早范订单结算",
							order.getPayPrice().subtract(account.getMoney())));
					payBean.setWeiPay(PayUtils.getWeiPayPackage(payBean.getOrderNo(), "早范", "早范订单结算",
							order.getPayPrice().subtract(account.getMoney())));
				} else {
					payBean.setAlipay(
							PayUtils.getAlipayPackageString(payBean.getOrderNo(), "早范", "早范订单结算", order.getPayPrice()));
					payBean.setWeiPay(
							PayUtils.getWeiPayPackage(payBean.getOrderNo(), "早范", "早范订单结算", order.getPayPrice()));
				}
			}
			payBean.setFreePrice(order.getFreePrice());
			payBean.setGoodsCount(order.getQuantity());
			payBean.setTotalPrice(order.getTotalPrice());
		} else {
			throw new ServiceException("订单信息有误!!");
		}
		return payBean;
	}

	public List<OrderInfo> exportTomorrowSendUserPhone() {
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, 1);
		int year = calendar.get(Calendar.YEAR);
		int month = calendar.get(Calendar.MONTH) + 1;
		int day = calendar.get(Calendar.DAY_OF_MONTH);
		try {
			String sql = "select * from " + OrderInfo.class.getSimpleName().toLowerCase() + " where sendDate = '" + year
					+ "-" + (month < 9 ? "0" + month : month + "") + "-" + (day < 9 ? "0" + day : day + "")
					+ "' and state in (" + StateConstant.ORDER_STATE_COMMENT + "," + StateConstant.ORDER_STATE_PAY
					+ ") and payState in ( " + StateConstant.ORDER_PAY_STATE_PAY + ","
					+ StateConstant.ORDER_PAY_STATE_ARCHIVE + ")" + " and isMuti = 0 group by accountMobile";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(OrderInfo.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public void updateWithOutPay(long time) {
		String sql = "update " + OrderInfo.class.getSimpleName().toLowerCase() + " set isTimeOut = 1 where "
				+ " state = " + StateConstant.ORDER_STATE_ORDER + " and payState = "
				+ StateConstant.ORDER_PAY_STATE_UN_PAY
				+ " and UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(orderTime) >= " + time;
		System.err.println(sql);
		QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
		try {
			runner.update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}




	public void payOrderByAdminMoney(String orderNo) throws ServiceException, SQLException {
		OrderInfo order = findByOrderNo(orderNo);
		if (order == null) {
			throw new ServiceException("订单信息错误!!");
		}
		Connection connection = C3p0DataSourceUtils.getConnection();
		try {
			connection.setAutoCommit(false);
			// 更新订单状态
			String sql = "update " + OrderInfo.class.getSimpleName().toLowerCase() + " set payState = "
					+ StateConstant.ORDER_PAY_STATE_PAY + ",state = " + StateConstant.ORDER_STATE_PAY
					+ ",payType='管理员操作支付' where orderNo = '" + order.getOrderNo() + "'";
			QueryRunner runner = new QueryRunner();
			runner.update(connection, sql);
			// 增加订单支付记录
			OrderPay orderPay = new OrderPay();
			orderPay.setAddTime(new Date());
			orderPay.setOrderId(order.getId());
			orderPay.setOrderNo(order.getOrderNo());
			orderPay.setTradeNo(order.getOrderNo());
			orderPay.setPayMethod("管理员操作支付");
			orderPay.setPayPrice(order.getPayPrice());
			orderPay.setPayState("支付成功");
			orderPay.setRemark("管理员操作支付");
			sql = SqlBuilderUtils.builderInsertSql(OrderPay.class, orderPay);
			runner.update(connection, sql);
			connection.commit();
		} catch (Exception e) {
			connection.rollback();
		} finally {
			connection.close();
		}
	}

	public boolean isInActivity(Date now) {
		try {
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			Date beginDate = df.parse("2015-12-26");
			Date endDate = df.parse("2016-01-31");
			if (now.after(beginDate) && now.before(endDate)) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return false;
	}

	public List<OrderInfo> exportTomorrowSendUserPhoneByShopId(Integer shopId) {
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, 1);
		int year = calendar.get(Calendar.YEAR);
		int month = calendar.get(Calendar.MONTH) + 1;
		int day = calendar.get(Calendar.DAY_OF_MONTH);
		try {
			String sql = "select * from " + OrderInfo.class.getSimpleName().toLowerCase() + " where sendDate = '" + year
					+ "-" + (month < 9 ? "0" + month : month + "") + "-" + (day < 9 ? "0" + day : day + "")
					+ "' and state in (" + StateConstant.ORDER_STATE_COMMENT + "," + StateConstant.ORDER_STATE_PAY
					+ ") and payState in ( " + StateConstant.ORDER_PAY_STATE_PAY + ","
					+ StateConstant.ORDER_PAY_STATE_ARCHIVE + ")" + " and isMuti = 0 and shopId = " + shopId
					+ " group by accountMobile";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(OrderInfo.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public List<OrderInfo> exportTodaySendUserMobile(Integer shopId) {
		Calendar calendar = Calendar.getInstance();
		int year = calendar.get(Calendar.YEAR);
		int month = calendar.get(Calendar.MONTH) + 1;
		int day = calendar.get(Calendar.DAY_OF_MONTH);
		try {
			String sql = "select * from " + OrderInfo.class.getSimpleName().toLowerCase() + " where sendDate = '" + year
					+ "-" + (month < 9 ? "0" + month : month + "") + "-" + (day < 9 ? "0" + day : day + "")
					+ "' and state in (" + StateConstant.ORDER_STATE_COMMENT + "," + StateConstant.ORDER_STATE_PAY
					+ ") and payState in ( " + StateConstant.ORDER_PAY_STATE_PAY + ","
					+ StateConstant.ORDER_PAY_STATE_ARCHIVE + ")" + " and isMuti = 0 and shopId = " + shopId
					+ " group by accountMobile";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(OrderInfo.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public List<ProductList> findTomorrowListBySendData(String sendDate, Integer shopId) {
		try {
			String sql = "select o.orderNo,o.id as orderId,d.productId,d.productName,sum(d.productCount) as productCount,d.shopName,d.shopId,d.productPrice from orderinfo o,orderdetail d where o.isMuti = 0 and o.shopId = "
					+ shopId + " and o.id = d.orderId and o.payState in (2,3) and sendDate = '" + sendDate
					+ "' group by d.productId";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(ProductList.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public List<ProductList> findOrderTemp() {
		try {
			String sql = "select o.orderNo,o.id as orderId,d.productId,d.productName,sum(d.productCount) as productCount,d.shopName,d.shopId,d.productPrice from orderinfo o,orderdetail d where o.isMuti = 0 and o.id = d.orderId and o.payState in (2,3) and date(o.orderTime) >= '2015-12-17' and date(o.orderTime) <= '2015-12-31' and accountMobile != '18016538177' group by d.productId;";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(ProductList.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public Long getTodaySellCount() {
		try {
			String sql = "select count(productId) from orderdetail where orderId in (select id from orderinfo where date(orderTime) = curdate() and payState in (2,3));";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new ScalarHandler<Long>());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0L;
	}

	public List<OrderChar> orderChar(String beginDate, String endDate) {
		try {
			String sql = "select sum(productCount) as count,productName,sum(productPrice * productCount) totalPrice from orderdetail where orderId in (select id from orderinfo where state in (1,2) and date(orderTime) <= '"
					+ endDate + "' and date(orderTime) >= '" + beginDate + "' and isMuti = 0) group by productId;";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(OrderChar.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public List<OrderInfo> exportTomorrowSendUserPhoneByAreaId(String areaId) {
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, 1);
		int year = calendar.get(Calendar.YEAR);
		int month = calendar.get(Calendar.MONTH) + 1;
		int day = calendar.get(Calendar.DAY_OF_MONTH);
		try {
			String sql = "select * from " + OrderInfo.class.getSimpleName().toLowerCase() + " where sendDate = '" + year
					+ "-" + (month < 9 ? "0" + month : month + "") + "-" + (day < 9 ? "0" + day : day + "")
					+ "' and state in (" + StateConstant.ORDER_STATE_COMMENT + "," + StateConstant.ORDER_STATE_PAY
					+ ") and payState in ( " + StateConstant.ORDER_PAY_STATE_PAY + ","
					+ StateConstant.ORDER_PAY_STATE_ARCHIVE + ")" + " and isMuti = 0 and areaId in (" + areaId + ") "
					+ " group by accountMobile";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(OrderInfo.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public List<OrderInfo> exportTodaySendUserMobile(String areaId) {
		Calendar calendar = Calendar.getInstance();
		int year = calendar.get(Calendar.YEAR);
		int month = calendar.get(Calendar.MONTH) + 1;
		int day = calendar.get(Calendar.DAY_OF_MONTH);
		try {
			String sql = "select * from " + OrderInfo.class.getSimpleName().toLowerCase() + " where sendDate = '" + year
					+ "-" + (month < 9 ? "0" + month : month + "") + "-" + (day < 9 ? "0" + day : day + "")
					+ "' and state in (" + StateConstant.ORDER_STATE_COMMENT + "," + StateConstant.ORDER_STATE_PAY
					+ ") and payState in ( " + StateConstant.ORDER_PAY_STATE_PAY + ","
					+ StateConstant.ORDER_PAY_STATE_ARCHIVE + ")" + " and isMuti = 0 and areaId in (" + areaId + ") "
					+ " group by accountMobile";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(OrderInfo.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public List<ProductList> findTomorrowListBySendData(String sendDate, String areaId) {
		try {
			String sql = "select o.orderNo,o.id as orderId,d.productId,d.productName,sum(d.productCount) as productCount,d.shopName,d.shopId,d.productPrice from orderinfo o,orderdetail d where o.isMuti = 0 and o.areaId in ("
					+ areaId + ") " + " and o.id = d.orderId and o.payState in (2,3) and sendDate = '" + sendDate
					+ "' group by d.productId";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			return runner.query(sql, new BeanListHandler<>(ProductList.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public void order_edit(String id, String sendDate, String sendTime, String userMobile, String consignee, String builderName, String floorNo) {
		String sql = "update " + OrderInfo.class.getSimpleName().toLowerCase() +
				" set sendDate = '"
				+sendDate+"',sendTime='"
				+sendTime+"',userMobile='"
				+userMobile+"',consignee='"
				+consignee+"',builderName='"
				+builderName+"',floorNo='"
				+floorNo+
				"' " +
				"where "
				+ " id = " + id ;
		System.err.println(sql);
		QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
		try {
			runner.update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void back(String id) {
		String sql = "update " + OrderInfo.class.getSimpleName().toLowerCase() +
				" set orderStatus = 111,state= 3,payState=4"
				+ " where "
				+ " id = " + id ;
		System.err.println(sql);
		QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
		try {
			runner.update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<OrderNotice> findOrderNotice(String id) {
		try {
			String sql = "select accountMobile mobile,productName,productCount " +
					"from orderdetail left join orderinfo on orderinfo.id = orderdetail.orderId " +
					"where sendDate=CURDATE() and state in(1,2) and cityId = " + id;
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(C3p0DataSourceUtils.getDataSource());
			List<OrderNotice> notices = runner.query(sql, new BeanListHandler<>(OrderNotice.class));
			for (OrderNotice notice : notices) {
				notice.setMobile(changeMobile(notice.getMobile()));
			}
			return notices;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	private String changeMobile(String mobile) {
		String newMobile = mobile.replaceAll("(\\d{3})\\d{4}(\\d{4})","$1****$2");
		return newMobile;
	}

	public List<OrderInfo> getCancelOrder(String orderNo,String mobile,String cityId,String payState,String begin,String end,String userMobile){

		System.err.println("chengshicityId:"+cityId);
		String sql = "select * from orderinfo where state = 3 and payState = "+payState;
		if(mobile!=null&&mobile!=""){
			sql = sql + " and accountMobile like "+"'%"+mobile+"%'";
		}
		if(userMobile!=null&&userMobile!=""){
			sql = sql + " and userMobile like "+"'%"+userMobile+"%'";
		}

		if(!"0".equals(cityId)){
			sql = sql + " and cityId = "+cityId;
		}
		if(orderNo!=""&&orderNo!=null){
			sql = sql + " and orderNo = "+ "'"+orderNo+"'";
		}
		sql = sql + " and parentId = 0 and orderStatus = 111";
		if(begin.equals("NaN")){
			begin = "0";

		}
		if (begin!="" && end !="" && begin!=null && end !=null)
		{
			sql = sql + " order by id desc " + "limit "+begin+","+end;
		}

		System.err.println(sql);
		QueryRunner runner = new QueryRunner(getDataSource());
		try {
			List<OrderInfo> list= runner.query(sql, new BeanListHandler<>(OrderInfo.class));

			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}
	public Long getCancelOrderCount(String orderNo,String mobile,String cityId,String payState,String userMobile){

		String sql = "select count(*) from orderinfo where state = 3 and payState = "+payState;
		if(mobile!=null&&mobile!=""){
			sql = sql + " and accountMobile like "+"'%"+mobile+"%'";
		}
		if(userMobile!=null&&userMobile!=""){
			sql = sql + " and userMobile like "+"'%"+userMobile+"%'";
		}
		if(!"0".equals(cityId)){
			sql = sql + " and cityId = "+cityId;
		}
		if(orderNo!=""&&orderNo!=null){
			sql = sql + " and orderNo = "+ "'"+orderNo+"'";
		}
		sql = sql + " and parentId = 0 and orderStatus = 111";
		System.err.println(sql);
		QueryRunner runner = new QueryRunner(getDataSource());
		try {
			Long count = runner.query(sql, new ScalarHandler<Long>());

			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0L;
	}
}
